<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %><!-- 数据库操作需要 -->        
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
		//获取参数，转换为相应的类型
		String RequestID=request.getParameter("RequestID");
		String Status="指派完成";
		String license_plate=request.getParameter("license_plate");
		String Did=request.getParameter("Did");
		String instructions=request.getParameter("instructions");
		String km=request.getParameter("km");
		String Userphone=null;
		String EmpID=null;
		String UseTime=null;
		String ReturnTime=null;
		String origin=null;
		String goPlace=null;
		String applicant=null;
		String Dname=null;
		String Dphone=null;
		int UsersNum=0;
		//System.out.println(" RequestID:"+ RequestID+"AgreeOrNot:"+AgreeOrNot);
		/*if(AgreeOrNot.equals("同意"))
			Status="审核通过";
		else
			Status="审核未通过";*/
		String ID=null;
		//第一个java代码段查询数据
		try{
			Class.forName("org.h2.Driver");//加载驱动
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return;
		}
		String url="jdbc:h2:d:/CompanyCar/temp/mydbtest";
		Connection conn=null;

	//数据库连接与查询过程中可能会引发sql异常
	try {
		//获取数据库连接(url,username,password)			
		conn = DriverManager.getConnection(url,"sa","");
		//准备执行数据库插入，此处使用了?方式，比拼接字符串更清晰
		String sql1="update CarRequest set Did=?,instructions=?,license_plate=?,km=?,Status=? where RequestID='"+RequestID+"' ";
		String sql2="select Userphone,EmpID,UseTime,ReturnTime,origin,goPlace,UsersNum from carrequest where RequestID='"+RequestID+"'";
		String sql4="insert into Driver_Task(Did,RequestID,UsePhone,applicant,UersNum,UseTime,ReturnTime,km,instructions) values(?,?,?,?,?,?,?,?,?)";
		String sql5="update EmployeeCarInfo set Did=?,Dname=?,Dphone=?,license_plate=? where RequestID='"+RequestID+"' ";
		String sql6="select Dname,Dphone from Driver where Did='"+Did+"'";
		//准备SQL执行;
		Statement stmt=conn.createStatement();
		//执行数据库查询，获取结果集
		ResultSet rs2=stmt.executeQuery(sql2);
		if (rs2.next()){
			Userphone=rs2.getString("Userphone");
			EmpID=rs2.getString("EmpID");
			UseTime=rs2.getString("UseTime");
			ReturnTime=rs2.getString("ReturnTime");
			origin=rs2.getString("origin");
			goPlace=rs2.getString("goPlace");
			UsersNum=Integer.parseInt(rs2.getString("UsersNum"));
		}
		String sql3="select Name from Employee where EmpID='"+EmpID+"'";
		ResultSet rs3=stmt.executeQuery(sql3);
		if (rs3.next()){
			applicant=rs3.getString("name");
		}
		ResultSet rs6=stmt.executeQuery(sql6);
		if (rs6.next()){
			Dname=rs6.getString("Dname");
			Dphone=rs6.getString("Dphone");
		}
		//关闭表达式对象
		stmt.close();	
		PreparedStatement pstmt=conn.prepareStatement(sql1);
		//准备SQL执行;注意此处与Statement的不同
		pstmt.setString(1,Did);
		pstmt.setString(2,instructions);
		pstmt.setString(3,license_plate);
		pstmt.setString(4,km);
		pstmt.setString(5,Status);
		//执行数据库插入，获取结果集
		int result=pstmt.executeUpdate();
		//pstmt.close();
		
		pstmt=conn.prepareStatement(sql4);
		pstmt.setString(1,Did);
		pstmt.setString(2,RequestID);
		pstmt.setString(3,Userphone);
		pstmt.setString(4,applicant);
		pstmt.setInt(5,UsersNum);
		pstmt.setString(6,UseTime);
		pstmt.setString(7,ReturnTime);
		pstmt.setString(8,km);
		pstmt.setString(9,instructions);
		result=pstmt.executeUpdate();
		//pstmt.close();
		
		pstmt=conn.prepareStatement(sql5);
		
		pstmt.setString(1,Did);
		pstmt.setString(2,Dname);
		pstmt.setString(3,Dphone);
		pstmt.setString(4,license_plate);
		result=pstmt.executeUpdate();
		pstmt.close();			
		//关闭数据库			
		conn.close();			
		
		
	} catch (SQLException e) {
		e.printStackTrace();
	} finally{
		//防止数据库未正常关闭
		try{
			if (conn!=null && (!conn.isClosed())){
				conn.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}

	request.setAttribute("addok","1");

	request.getRequestDispatcher("/D-car_manage.jsp").forward(request,response);
%>
</body>
</html>